import logging
from sqlalchemy import Result, select, text
from sqlalchemy.ext.asyncio import AsyncSession

from price_analysis.utils.file_support import JsonFileSupport
from price_analysis.core.config import settings

from price_analysis.api_v1.offer.schemas import ItemBase, ItemList


main_logger = logging.getLogger(__name__)
logger = main_logger.getChild("Request Offers")


async def get_items() -> ItemList:
    logger.info("Получение списка позиций")
    full_path_to_request_offer = f"{settings.ocr_data_path}/to_request_offer.json"
    data_for_offer = JsonFileSupport(full_path_to_request_offer)
    data = await data_for_offer.read_json_file()

    return ItemList(data)


async def get_sorted_items(session: AsyncSession, items: ItemList) -> list[dict]:
    logger.info("Разбивка позиций по категориям")
    sorted_items = []
    for section in items.sections:
        sorted_item = {}
        stmt = text(
            """
            SELECT category.id
            FROM category
            JOIN section_types st on category.id = st.category_id
            WHERE st.name = :section_type 
            """
        )

        result: Result = await session.execute(stmt, {"section_type": section})
        category_id = result.fetchone()[0]

        stmt = text("SELECT * FROM provider WHERE category_id = :category_id")
        result: Result = await session.execute(stmt, {"category_id": category_id})
        result_set = result.fetchall()
        providers = [
            {"name": row[1], "e_mail": row[2], "phone": row[3], "man": row[4]}
            for row in result_set
        ]
        data = [item for item in items.items if item["section_title"] == section]

        sorted_item["section"] = section
        sorted_item["category_id"] = category_id
        sorted_item["providers"] = providers
        sorted_item["data"] = data

        sorted_items.append(sorted_item)

    return sorted_items
